﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using WindowsFormsApplication1.DTO;

namespace WindowsFormsApplication1.DAO
{
    class BienBanCoiThiDAO
    {
        public static bool Insert(BienBanCoiThiDTO bbdto)
        {
            SqlConnection ketNoi = AbstractDAO.Connection();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = ketNoi;
            cmd.CommandText = @"INSERT INTO BIENBANCOITHI(Lop,HocPhan,ThoiGian,NgayThi,NamHoc,HocKy,HeDaoTao,LanThi) VALUES ('" + bbdto.lop + "', '" + bbdto.hocPhan + "', " + bbdto.thoiGian + ", '"
                + bbdto.ngayThi.ToShortDateString() + "'," + bbdto.namHoc + ", " + bbdto.hocKy + "," + bbdto.heDaoTao + "," + bbdto.lanThi+")";
            if (cmd.ExecuteNonQuery() != -1)
                return true;
            return false;
        }
        public static int getMaxID()
        {
            int maxID = 0;
            SqlConnection ketNoi = AbstractDAO.Connection();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = ketNoi;
            cmd.CommandText = @"SELECT MAX(ID) FROM BIENBANCOITHI";
            SqlDataReader reader = cmd.ExecuteReader();
            reader.Read();
            maxID = int.Parse(reader[0].ToString());
            reader.Close();
            return maxID;
        }

        public static float TinhCongCoiThi(string maGiaoVien, int namHoc, int hocKy)
        {
            SqlConnection ketNoi = AbstractDAO.Connection();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = ketNoi;
            cmd.CommandText = @"select a.LanThi, a.ThoiGian from bienbancoithi a, chitietcoithi b where a.id = b.ID_BienBan and b.ID_GiaoVien = '" + maGiaoVien + "' and a.NamHoc = " + namHoc + " and a.HocKy = " + hocKy;

            float cong = 0;
            int maLoaiGiaoVien = GiaoVienDAO.GetMaLoaiGiaoVien(maGiaoVien);
            SqlDataReader boDoc = cmd.ExecuteReader();
            while (boDoc.Read())
            {
                int lanThi = boDoc.GetInt32(0);
                int thoiGian = boDoc.GetInt32(1);
                switch (lanThi)
                {
                    case 1:
                        if (maLoaiGiaoVien != Def.GV_MOIGIANG)
                            cong += (float)(thoiGian / 60f * 0.9);
                        break;
                    case 2:
                        cong += (float)(thoiGian / 60f * 0.9);
                        break;
                    case 3:
                        cong += (float)(thoiGian / 60f * 0.45);
                        break;
                }
            }
            return cong;
        }
    }
}
